﻿using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using MiniExcelLibs;
using MiniExcelLibs.Attributes;
using NPOI.HSSF.UserModel;
using NPOI.OpenXmlFormats.Spreadsheet;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Reflection;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Model;

namespace EFDemo.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class NpoiController : ControllerBase
    {
        [HttpPost]
        public void DaoRu(IFormFile file)
        {
            HSSFWorkbook wb = new HSSFWorkbook(file.OpenReadStream());
            ISheet sheet = wb.GetSheet("sheet1");

            var aaa = typeof(ExcelModel).GetProperties();
            List<ExcelModel> list = new List<ExcelModel>();

            int rows = sheet.LastRowNum;
            for (int i = 1; i <= rows; i++)
            {
                IRow row = sheet.GetRow(i);
                ExcelModel entity = new ExcelModel();
                foreach (PropertyInfo item in aaa)
                {
                    var bbb = item.GetCustomAttribute<ExcelColumnAttribute>();
                    var value = row.GetCell(bbb.Index);
                    if (value.CellType == CellType.Numeric)
                    {
                        if (item.PropertyType == typeof(string))
                        {
                            item.SetValue(entity, value.NumericCellValue.ToString());
                        }
                        else
                        {
                            item.SetValue(entity, value.NumericCellValue);
                        }
                    }
                    else if (value.CellType == CellType.String)
                    {
                        item.SetValue(entity, value.StringCellValue);
                    }
                }
                list.Add(entity);
            }

        }

        [HttpGet]
        public void DaoChu()
        {
            var aaa = typeof(ExcelModel).GetProperties();


            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("sheet1");

            IRow first = sheet.CreateRow(0);
            for (int i = 0; i < aaa.Length; i++)
            {
                var bbb = aaa[i].GetCustomAttribute<ExcelColumnNameAttribute>()?.ExcelColumnName ?? "";
                first.CreateCell(i).SetCellValue(bbb);
            }

            ISheet drowdown = wb.CreateSheet("111");
            SetDropdown(drowdown, 1, 1, new List<string>() { "是", "否" }.ToArray());

            using (FileStream stream = new FileStream("C:\\Users\\admin\\Desktop\\11.xls", FileMode.OpenOrCreate))
            {
                wb.Write(stream);
            }
        }

        [HttpGet]
        public void DaoChuXlsx()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("sheet1");
            //SetDropdown(sheet, 1, 1, new List<string>() { "是", "否" }.ToArray());

            using (FileStream stream = new FileStream("C:\\Users\\admin\\Desktop\\11.xlsx", FileMode.OpenOrCreate))
            {
                wb.Write(stream);
            }
        }

        [HttpGet]
        public void A11()
        {
            //创建Excel文件的对象(调用NPOI文件)
            XSSFWorkbook workbook = new XSSFWorkbook();
            //设置标题样式
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            style.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");//单元格格式设置为文本
            //设置普通内容样式
            ICellStyle style1 = workbook.CreateCellStyle();
            style1.Alignment = HorizontalAlignment.Left;
            style1.VerticalAlignment = VerticalAlignment.Center;
            style1.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");//单元格格式设置为文本

            Dictionary<string, string[]> fieldItms = new Dictionary<string, string[]>();
            fieldItms.Add("性别",new string[] { "男", "女" });
            fieldItms.Add("国家", new string[] { "中国", "外国" });
            fieldItms.Add("省", new string[] { "台湾", "新疆" });

            Dictionary<string, string> titleFileds = new Dictionary<string, string>();
            titleFileds.Add("姓名","姓名");
            titleFileds.Add("年龄", "年龄");
            titleFileds.Add("性别", "性别");
            titleFileds.Add("国家", "国家");
            titleFileds.Add("省", "省");

            bool isValidation = true;

            //创建Excel工作表
            var sheet1 = (XSSFSheet)workbook.CreateSheet("Template");
            //字段的Excelz字段取值范围
            var fieldRanges = AddFieldItemsSheel(workbook, style1, titleFileds, fieldItms);
            IRow row1 = sheet1.CreateRow(0);//创建标题行
            int col = 0;//列
            foreach (var field in titleFileds)//循环字段
            {
                sheet1.SetDefaultColumnStyle(col, style1);//设置列样式
                sheet1.SetColumnWidth(col, 20 * 256);//设置宽度
                row1.CreateCell(col).CellStyle = style;//设置标题行的第col列样式
                row1.GetCell(col).SetCellValue(field.Value);//设置标题行的第col列内容

                if (fieldItms != null & isValidation)//isValidation是否开启字段下拉项校验,fieldItms字段校验项
                {
                    //判断字段是否是选项
                    if (fieldItms.ContainsKey(field.Key))//判断当前字段是否有下拉项
                    {
                        var cellRegions = new CellRangeAddressList(1, 65535, col, col);//设置区域,第2~65535行,col~col列
                        XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1);
                        //创建约束
                        var dropDownConstraint = helper.CreateFormulaListConstraint(fieldRanges[field.Key]);
                        //创建验证规则
                        IDataValidation dropDownValidation = helper.CreateValidation(dropDownConstraint, cellRegions);
                        //设置约束提示信息
                        dropDownValidation.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");
                        dropDownValidation.ShowErrorBox = true;
                        sheet1.AddValidationData(dropDownValidation);
                    }
                }
                col++;
            }

            using (FileStream stream = new FileStream("C:\\Users\\admin\\Desktop\\11.xlsx", FileMode.OpenOrCreate))
            {
                workbook.Write(stream);
            }
        }

        private Dictionary<string, string> AddFieldItemsSheel(XSSFWorkbook workbook, ICellStyle style
            ,Dictionary<string,string> titleFileds, Dictionary<string, string[]> fieldItms)
        {
            Dictionary<string, string> dic = new Dictionary<string, string>();
            if (fieldItms != null)
            {
                ISheet sheet = workbook.CreateSheet("FieldItems");//创建Excel第二页
                var col = 0;
                foreach (var item in fieldItms)//字段下拉项
                {
                    for (var i = 0; i <= item.Value.Length; i++)//因为要留出标题行，所以这里是<=
                    {
                        var row = sheet.GetRow(i);
                        if (row == null)
                        {
                            row = sheet.CreateRow(i);
                        }
                        var cell = row.CreateCell(col);
                        if (i == 0) //第一行为标题
                        {
                            cell.CellStyle = style;//标题样式
                            cell.SetCellValue(titleFileds[item.Key]);//标题
                        }
                        else
                        {
                            cell.SetCellValue(item.Value[i - 1]);//下拉项值
                        }
                    }
                    col++;
                    //取Excle列  1=A  2=b  27=AA
                    var dividend = col;
                    string cName = string.Empty;
                    while (dividend > 0)
                    {
                        var modulo = (dividend - 1) % 26;
                        cName = Convert.ToChar(65 + modulo) + cName;
                        dividend = (dividend - modulo) / 26;
                    }
                    //生成下拉项取值范围：$FieldItems!$A$2:$A$10
                    //范围应该是$FieldItems!A2:A10，因为Excel会根据规律改值，第二行就会变成A3:A11,所以要加上$符号，定死
                    dic.Add(item.Key, $"FieldItems!${cName}$2:${cName}${item.Value.Length + 1}");
                }
            }
            return dic;
        }

        private void SetDropdown(ISheet sheet, int first, int last, string[] values)
        {
            var cell = new CellRangeAddressList(1, 65535, first, last);
            DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(values);
            HSSFDataValidation validation = new HSSFDataValidation(cell, constraint);
            validation.CreateErrorBox("输入不合法", "请输入下拉框的值");
            validation.ShowPromptBox = true;

            sheet.AddValidationData(validation);
        }

        private void SetDropdown(XSSFSheet? sheet, int first, int last, string[] values)
        {
            var cell = new CellRangeAddressList(1, 65535, first, last);
            XSSFDataValidationHelper helper = new XSSFDataValidationHelper(sheet);
            var constraint = helper.CreateExplicitListConstraint(values);
            //constraint = helper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, "1", "10");
            IDataValidation validation = helper.CreateValidation(constraint, cell);
            validation.CreateErrorBox("输入不合法", "请输入下拉框的值");
            validation.ShowErrorBox = true;

            sheet.AddValidationData(validation);
        }
    }

    public class ExcelModel
    {
        /// <summary>
        /// 获取编码
        /// </summary>
        [ExcelColumn(Name = "获取编码", Index = 0)]
        public string Code { get; set; }

        /// <summary>
        /// 获取名称
        /// </summary>
        [ExcelColumn(Name = "获取名称", Index = 1)]
        public string Name { get; set; }
    }
}
